#!/usr/bin/python
# -*- coding: utf-8 -*-  
"""
@Project : hello 
@file : OracleQueryExecutor.py
@Author : sheen
@time : 2025/5/22 10:11
@func :
"""
import logging
import time
import json
import oracledb
from typing import List, Dict, Any, Generator, Optional
from contextlib import contextmanager
from com.cn.for_cdc.common.cdc_conmons import oracle_adm_connections
from com.cn.for_cdc.common.log_helper import configure_logging


class OracleQueryExecutor:
    """Oracle数据库查询执行器（支持连接池和性能监控）"""

    def __init__(self, config: Dict[str, Any], pool_min: int = 2, pool_max: int = 10):
        """
        初始化连接池配置
        :param config: 数据库连接配置字典
        :param pool_min: 连接池最小连接数
        :param pool_max: 连接池最大连接数
        """
        self._pool = oracledb.SessionPool(
            user=config["user"],
            password=config["password"],
            dsn=config["dsn"],
            min=pool_min,
            max=pool_max,
            increment=2,
            encoding="UTF-8",
            threaded=True
        )
        logging.info(f"初始化Oracle连接池成功，当前连接数：{self._pool.opened}")

    @contextmanager
    def _get_connection(self) -> Generator[oracledb.Connection, None, None]:
        """获取数据库连接的上下文管理器"""
        conn = self._pool.acquire()
        try:
            yield conn
        finally:
            self._pool.release(conn)
            logging.debug(f"释放连接,  当前连接池状态 | 已用连接: {self._pool.busy} | 空闲连接: {self._pool.opened - self._pool.busy}")

    def _format_result(self, cursor: oracledb.Cursor) -> List[Dict[str, Any]]:
        """格式化查询结果为字典列表"""
        columns = [col[0] for col in cursor.description]
        return [dict(zip(columns, row)) for row in cursor]

    def execute_query(
            self,
            sql: str,
            params: Optional[Dict[str, Any]] = None,
            fetch_all: bool = True
    ) -> List[Dict[str, Any]]:
        """
        执行SQL查询
        :param sql: SQL语句（支持命名参数）
        :param params: 参数字典
        :param fetch_all: 是否获取全部结果
        :return: 格式化后的结果集
        """
        start_time = time.time()
        try:
            with self._get_connection() as conn, conn.cursor() as cursor:
                cursor.execute(sql, parameters=params)
                result = self._format_result(cursor)
                elapsed = time.time() - start_time
                logging.info(f"执行成功 | 耗时:{elapsed:.2f}s | 返回行数:{len(result)}")
                if logging.getLogger().isEnabledFor(logging.DEBUG):
                    logging.debug(f"完整结果:\n{json.dumps(result, indent=2)}")
                return result if fetch_all else result[:1]
        except oracledb.DatabaseError as e:
            error = f"执行失败 | SQL:\n{sql}\n参数:{params}\n错误:{e}"
            logging.error(error)
            raise RuntimeError(error)

    def batch_execute(
            self,
            sql_statements: List[str],
            params_list: Optional[List[Dict[str, Any]]] = None
    ) -> List[List[Dict[str, Any]]]:
        """批量执行SQL语句"""
        results = []
        total_start = time.time()

        try:
            with self._get_connection() as conn:
                for i, sql in enumerate(sql_statements):
                    params = params_list[i] if params_list else None
                    with conn.cursor() as cursor:
                        cursor.execute(sql, parameters=params)
                        results.append(self._format_result(cursor))
            total_elapsed = time.time() - total_start
            logging.info(f"批量执行完成 | 总耗时:{total_elapsed:.2f}s | 语句数:{len(sql_statements)}")
            return results
        except oracledb.DatabaseError as e:
            error = f"批量执行失败 | 错误:{e}"
            logging.error(error)
            raise RuntimeError(error)

    def __del__(self):
        """关闭连接池"""
        if hasattr(self, "_pool"):
            self._pool.close()
            logging.info("Oracle连接池已关闭")


def main():
    """使用示例"""
    configure_logging()

    # 初始化执行器
    config = oracle_adm_connections['icibe_pro']
    executor = OracleQueryExecutor(config)

    # 复杂查询示例
    complex_sql = """
    SELECT /*+ parallel(16) */ 
        sc.ROW_ID AS contact_id,
        NVL(X_SUPP_TP_INT_FLG,'N')
    FROM siebel.s_contact@SBL sc
    WHERE sc.BU_ID = '1-7V7V'
      AND sc.LAST_UPD < sysdate - 1/12
    MINUS 
    SELECT /*+ parallel(16) */
        sbc.CONTACT_ID,
        NVL(sbc.SUPP_TP_INT_FLG ,'N')
    FROM STG_B_CONTACT sbc
    """

    # 执行单个查询
    try:
        start = time.time()
        result = executor.execute_query(complex_sql)
        logging.info(f"获取到{len(result)}条差异数据")
        logging.info(f"总执行耗时: {time.time() - start:.2f}s")
    except Exception as e:
        logging.error(f"主流程执行异常: {e}")


if __name__ == "__main__":
    main()
